package cn.edu.cqu.fredyvia.forum.mapper;

import cn.edu.cqu.fredyvia.forum.dto.TalkUserinfoDTO;
import cn.edu.cqu.fredyvia.forum.entity.Talk;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author fredyvia
 * @since 2021-07-10
 */
@Mapper
public interface TalkMapper extends BaseMapper<Talk> {


//    @Select("SELECT `user`.username, `user_info`.avatar, `talk`.* ,total FROM ((`user_info` RIGHT JOIN `user` ON `user_info`.user_id = `user`.id) INNER JOIN `talk` ON `user`.id = `talk`.user_id) LEFT JOIN (SELECT `talk_thumb`.talk_id,COUNT(*) AS total FROM `talk_thumb` GROUP BY `talk_thumb`.talk_id) as tmp ON `tmp`.talk_id = `talk`.id ORDER BY `talk`.nice DESC,`tmp`.total DESC,`talk`.create_datetime DESC LIMIT #{from},#{to};")
//    List<TalkUserinfoDTO> select_inject(@Param("from") Integer from, @Param("to")Integer to);


    @Select("SELECT `user`.username, `user_info`.avatar, `talk`.*,total_thumb,total_comment FROM ((`user_info` RIGHT JOIN `user` ON `user_info`.user_id = `user`.id) INNER JOIN `talk` ON `user`.id = `talk`.user_id) LEFT JOIN (SELECT `talk_thumb`.talk_id,COUNT(*) AS total_thumb FROM `talk_thumb` GROUP BY `talk_thumb`.talk_id) as tmp1 ON `tmp1`.talk_id = `talk`.id LEFT JOIN (SELECT  talk_id,COUNT(*) AS total_comment FROM `comment` GROUP BY `comment`.talk_id) as tmp2 ON `tmp2`.talk_id = `talk`.id  ORDER BY `talk`.nice DESC,create_datetime DESC,${type} DESC LIMIT ${from},${to};")
    List<TalkUserinfoDTO> select_inject(@Param("from") Integer from, @Param("to")Integer to,@Param("type") String type);

    @Select("SELECT `user_info`.avatar, `user`.username, `talk`.* FROM (`user` LEFT JOIN `user_info` ON `user`.id = `user_info`.user_id ) INNER JOIN `talk` ON `user`.id = `talk`.user_id WHERE `talk`.id = ${talkId};")
    TalkUserinfoDTO getUserinfoDto(@Param("talkId") Integer talkId);
//    @Select("SELECT `user`.username, `user_info`.avatar, `talk`.* FROM (`user_info` INNER JOIN `user` ON `user_info`.user_id = `user`.id) INNER JOIN `talk` ON `user`.id= `talk`.user_id ORDER BY #{type} DESC LIMIT #{from},#{to};")
//    List<TalkUserinfoDTO> select_inject(@Param("from") Integer from, @Param("to")Integer to, @Param("type")String type);
//    @Select("SELECT `user`.username, `user_info`.avatar, `talk`.* FROM (`user_info` INNER JOIN `user` ON `user_info`.user_id = `user`.id) INNER JOIN `talk` ON `user`.id= `talk`.user_id ORDER BY #{type} DESC LIMIT #{from},#{to};")
//    List<TalkUserinfoDTO> sortList(@Param("type")String type,@Param("from") Integer from, @Param("to")Integer to);

}
